package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.fact.dto.MakeDTO;
import com.fact.dto.NodeHierarchyDTO;
import com.fact.dto.RoleDTO;
import com.fact.dto.ScenarioDTO;
import com.fact.utils.connection.ConnectionManager;

public class NodeHierarchyService {
	
	
	public ArrayList<NodeHierarchyDTO> getNodeList(int segmentId)
	{
		ArrayList<NodeHierarchyDTO> nodeList=new ArrayList<NodeHierarchyDTO>();
		Connection conn = ConnectionManager.getConnection();
		   String sql = " Select tmp2.*,MST_SEGMENTS.SEGMENT_NAME,NODE_TYPE.TYPE_NAME from  " +
				"(Select tmp1.* from  " +
				"( " +
				"SELECT Lpad(label,Length(label) + LEVEL * 10 - 10,'-') vizstring,label node_name,DESCRIPTION,node_id,data_type,position,parent_node,node_type_id,segment_id,label,rownum indx " +
				"FROM   DB_FACTENTRY.Node_hierarchy " +
				"START WITH parent_node is null " +
				"CONNECT BY PRIOR  node_id=parent_node " +
				")tmp1 Where segment_id=? " +
				")tmp2,MST_SEGMENTS,NODE_TYPE " +
				"Where tmp2.NODE_TYPE_ID=NODE_TYPE.NODE_TYPE_ID " +
				"And   tmp2.SEGMENT_ID=MST_SEGMENTS.SEGMENT_ID " +
				"Order by tmp2.indx ";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, segmentId);
				
				r = stmt.executeQuery();
				NodeHierarchyDTO n=null;
				while (r.next())
				{
					n=new NodeHierarchyDTO();
					n.setNodeId(r.getInt("node_id"));
					n.setNodeName(r.getString("node_name"));
					n.setVizString(r.getString("vizstring"));
					n.setDataType(r.getString("data_type"));
					n.setDescription(r.getString("DESCRIPTION"));					
					n.setParentNode(r.getInt("parent_node"));
					n.setNodeTypeId(r.getInt("node_type_id"));
					n.setSegmentId(r.getInt("segment_id"));
					n.setSegmentName(r.getString("SEGMENT_NAME"));
					n.setNodeTypeName(r.getString("TYPE_NAME"));
					n.setLabel(r.getString("LABEL"));
					
					
					nodeList.add(n);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return nodeList;
	}
	
	public ArrayList<NodeHierarchyDTO> getKpiNodeList(int segmentId)
	{
		ArrayList<NodeHierarchyDTO> nodeList=new ArrayList<NodeHierarchyDTO>();
		Connection conn = ConnectionManager.getConnection();
		   String sql = " Select node_id,label from NODE_HIERARCHY Where Segment_Id=? and Node_Type_id=2 Order By Label";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, segmentId);
				
				r = stmt.executeQuery();
				NodeHierarchyDTO n=null;
				while (r.next())
				{
					n=new NodeHierarchyDTO();
					n.setNodeId(r.getInt("node_id"));
					n.setNodeName(r.getString("LABEL"));
					nodeList.add(n);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return nodeList;
	}
	
	public ArrayList<NodeHierarchyDTO> getParentNodeList(int segmentId)
	{
		ArrayList<NodeHierarchyDTO> nodeList=new ArrayList<NodeHierarchyDTO>();
		Connection conn = ConnectionManager.getConnection();
		   String sql = " Select node_id,label from NODE_HIERARCHY Where Segment_Id=? and Node_Type_id in (1,4) Order By Label";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, segmentId);
				
				r = stmt.executeQuery();
				NodeHierarchyDTO n=null;
				while (r.next())
				{
					n=new NodeHierarchyDTO();
					n.setNodeId(r.getInt("node_id"));
					n.setNodeName(r.getString("LABEL"));
					nodeList.add(n);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return nodeList;
	}
	
	public boolean saveNode(NodeHierarchyDTO node,ArrayList<MakeDTO> makeList,ArrayList<ScenarioDTO> scenarioList)
	{

		int nodeId=0;
		Connection conn = ConnectionManager.getConnection();
		String sql = " Select SQN_NODE.NEXTVAL NODEID from dual";
		   boolean response=false;
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				if(r.next())
					nodeId=r.getInt("NODEID");
				
				sql="Insert into NODE_HIERARCHY " +
						"(NODE_ID,DATA_TYPE,LABEL,DESCRIPTION,POSITION,PARENT_NODE,NODE_TYPE_ID,SEGMENT_ID) " +
						" Values(?,?,?,?,?,?,?,?)";  
				
				stmt = conn.prepareStatement(sql);
				
				stmt.setInt(1, nodeId);
				stmt.setString(2, node.getDataType());
				stmt.setString(3, node.getLabel());
				stmt.setString(4, node.getDescription());
				stmt.setInt(5, 100);		
				
				if(node.getParentNode()==0)
					stmt.setNull(6, java.sql.Types.NULL);
				else
					stmt.setInt(6, node.getParentNode());
				
				stmt.setInt(7, node.getNodeTypeId());
				stmt.setInt(8, node.getSegmentId());
				
				r = stmt.executeQuery();
				
				if(node.getNodeTypeId()==2)
				{
					for(int i=0;i<makeList.size();i++)
					{
						for(int j=0;j<scenarioList.size();j++)
						{
							
							sql="Insert into NODE_SCENARIO_ATTRIBUTES " +
									"(MAKE_ID,NODE_ID,SCENARIO_ID,MAX_VALUE,MIN_VALUE,VISIBILITY_FLAG) " +
									" Values(?,?,?,?,?,?)";  
							stmt = conn.prepareStatement(sql);
							
							stmt.setInt(1, makeList.get(i).getMakeId());
							stmt.setInt(2, nodeId);
							stmt.setInt(3, scenarioList.get(j).getScenarioId());
							stmt.setInt(4, 999999);
							stmt.setInt(5, 0);
							stmt.setInt(6, 1);
							stmt.executeUpdate();
							
						}
					}
				}
				response=true;
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
		return response;
	}
	
	public boolean editNode(NodeHierarchyDTO node)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update Node_Hierarchy Set Data_Type=?,Description=?,label=?,parent_Node=?, " +
				" Node_Type_Id=? Where Node_Id=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1, node.getDataType());
				stmt.setString(2, node.getDescription());
				stmt.setString(3, node.getLabel());
				
				if(node.getParentNode()==0)
					stmt.setNull(4, java.sql.Types.NULL);
				else
					stmt.setInt(4, node.getParentNode());
				
				stmt.setInt(5, node.getNodeTypeId());
				stmt.setInt(6, node.getNodeId());
				
				response =  stmt.executeUpdate();
				
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	
	public boolean deleteNode(int nodeId) 
	{
		Connection conn = ConnectionManager.getConnection();
		
		String sql_nsa="Delete NODE_SCENARIO_ATTRIBUTES Where Node_Id=?";
		String sql_na=" Delete NODE_HIERARCHY Where NODE_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql_nsa);
				stmt.setInt(1,nodeId);
				stmt.executeUpdate();
				
				stmt = conn.prepareStatement(sql_na);
				stmt.setInt(1,nodeId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	
	}
	
	public String getNodeListString(int segmentId)
	{
		ArrayList<NodeHierarchyDTO> nodeList=getKpiNodeList(segmentId);	
		String nodeListStr="<select  name='user.userTypeId' id='segmentId' class='nostyle' style='width:100%;' onchange=\"nodeAttributeList(this.value)\">";
		nodeListStr+="<option value='none'>Select KPI</option>";
		for(int i=0;i<nodeList.size();i++)
		{
			nodeListStr+="<option value='"+nodeList.get(i).getNodeId()+"'>"+nodeList.get(i).getNodeName()+"</option>";
		}
		nodeListStr+="</select>";
		
		return nodeListStr;

	}
	
	public String getParentNodeListString(int segmentId,int parentNode)
	{
		ArrayList<NodeHierarchyDTO> nodeList=getParentNodeList(segmentId);	
		String nodeListStr="<select  name='node.parentNode' id='parentNod' class='nostyle' style='width:100%;' >";
		nodeListStr+="<option></option>";
		for(int i=0;i<nodeList.size();i++)
		{
			if(parentNode==-1)
			{
				nodeListStr+="<option value='"+nodeList.get(i).getNodeId()+"'>"+nodeList.get(i).getNodeName()+"</option>";
			}
			else
			{
			  if(nodeList.get(i).getNodeId()==parentNode)
			  {
				  nodeListStr+="<option value='"+nodeList.get(i).getNodeId()+"' selected='selected'>"+nodeList.get(i).getNodeName()+"</option>";
			  }
			  else
			  {
				  nodeListStr+="<option value='"+nodeList.get(i).getNodeId()+"'>"+nodeList.get(i).getNodeName()+"</option>";
			  }
			}
			
		}
		nodeListStr+="</select>";
		
		return nodeListStr;

	}
	
}
